{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['dsfasdf \\tdasfasdf\\tgfgfgf\\tjjfgjhfvbnm\\tddf\\tfghfgd']\n",
      "['234\\t5\\t42424\\t554545\\t4625\\t12321']\n",
      "['234\\t5\\t6234\\t335435\\t5345\\t21312']\n",
      "['234\\t5\\t6234\\t6\\t56\\t234234']\n",
      "['234\\t5\\t6234\\t23\\tty252\\twerwer']\n",
      "['234\\t5\\t6234\\t23\\t23423\\twsfwd']\n",
      "['234\\t5\\t6234\\t423543\\t23423\\t23423']\n",
      "['234\\t5\\t6234\\t6547\\t345634\\t23423']\n",
      "['234\\t5\\t6234\\t234\\t414\\t323']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "with open('../data/bbc.csv','r') as fp:\n",
    "    reader = csv.reader(fp)\n",
    "    \n",
    "    for x in reader:\n",
    "        name=x\n",
    "        print(name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据库连接与插入数据(增)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#数据库连接\n",
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "db=pymysql.connect(   host='127.0.0.1',\n",
    "    user='root',\n",
    "    password='7923161' ,\n",
    "    database='pymysql_demo',\n",
    "    port=3306,)\n",
    " #demo3\n",
    "#insert into user(id,username,age,password) values(2,'bbb',20,1111)\n",
    "cursor = db.cursor()\n",
    "sql=\"\"\"\n",
    "insert into user(id,username,age,password) values(null,%s,%s,%s)\n",
    "\"\"\"\n",
    "username = 'oldman'\n",
    "\n",
    "age= 23\n",
    "\n",
    "password='12345678'\n",
    "cursor.execute(sql,(username,age,password))\n",
    "db.commit()\n",
    "db.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 查找数据(查)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# 查找数据：\n",
    "使用pymysql查询数据。使用可以fetch*方法。\n",
    "\n",
    "1.fetchone()：这个方法每次之获取一条数据。\n",
    "2,fetchall()：这个方法接收全部的返回结果。\n",
    "3,fetchmany(size)：电子杂志可以指定条数的数据\n",
    "示例代码如下："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'aaa', 18, '111111')\n",
      "(2, 'bbb', 20, '1111111')\n",
      "(3, 'batman', 22, '12345678')\n",
      "(4, 'oldman', 23, '12345678')\n",
      "(5, 'oldman', 23, '12345678')\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "conn=pymysql.connect(   host='127.0.0.1',\n",
    "    user='root',\n",
    "    password='7923161' ,\n",
    "    database='pymysql_demo',\n",
    "    port=3306,)\n",
    "\n",
    "#select username,age from user where id=1\n",
    "#select * from user\n",
    "cursor=conn.cursor()\n",
    "sql =\"\"\"select* from user\"\"\"\n",
    "cursor.execute(sql)\n",
    "while True:\n",
    "    result = cursor.fetchone()\n",
    "    if result:\n",
    "        print(result)\n",
    "    else:\n",
    "        break\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'aaa', 18, '111111')\n",
      "(2, 'bbb', 20, '1111111')\n",
      "(3, 'batman', 22, '12345678')\n",
      "(4, 'oldman', 23, '12345678')\n",
      "(5, 'oldman', 23, '12345678')\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "conn=pymysql.connect(   host='127.0.0.1',\n",
    "    user='root',\n",
    "    password='7923161' ,\n",
    "    database='pymysql_demo',\n",
    "    port=3306,)\n",
    "cursor=conn.cursor()\n",
    "sql =\"\"\"select* from user\"\"\"\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchall()\n",
    "for result in results:\n",
    "    print(result)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'aaa', 18, '111111')\n",
      "(2, 'bbb', 20, '1111111')\n",
      "(3, 'batman', 22, '12345678')\n"
     ]
    }
   ],
   "source": [
    "import pymysql\n",
    "conn=pymysql.connect(   host='127.0.0.1',\n",
    "    user='root',\n",
    "    password='7923161' ,\n",
    "    database='pymysql_demo',\n",
    "    port=3306,)\n",
    "cursor=conn.cursor()\n",
    "sql =\"\"\"select* from user\"\"\"\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchmany(3)\n",
    "for result in results:\n",
    "    print(result)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 删除数据(删)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "conn=pymysql.connect(   host='127.0.0.1',\n",
    "    user='root',\n",
    "    password='7923161' ,\n",
    "    database='pymysql_demo',\n",
    "    port=3306,)\n",
    "cursor = conn.cursor()\n",
    "sql='''\n",
    "delete from user where id =1\n",
    "'''\n",
    "cursor.execute(sql)\n",
    "#增删改都需要执行commit操作\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 改数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "conn=pymysql.connect(   host='127.0.0.1',\n",
    "    user='root',\n",
    "    password='7923161' ,\n",
    "    database='pymysql_demo',\n",
    "    port=3306,)\n",
    "cursor = conn.cursor()\n",
    "sql='''\n",
    "update user set password='yaozhenneng'\n",
    "'''\n",
    "\n",
    "cursor.execute(sql)\n",
    "#增删改都需要执行commit操作\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
